CREATE DATABASE club_management;

USE club_management;

CREATE TABLE Student (
    student_id      INT PRIMARY KEY,
    first_name      VARCHAR(50)      NOT NULL,
    last_name       VARCHAR(50)      NOT NULL,
    email           VARCHAR(100)     NOT NULL UNIQUE,
    course          VARCHAR(100),
    year_of_study   INT
);

CREATE TABLE Club (
    club_id         INT PRIMARY KEY,
    club_name       VARCHAR(100)     NOT NULL UNIQUE,
    category        VARCHAR(50),
    founding_date   DATE,
    description     TEXT
);

CREATE TABLE Membership (
    membership_id   INT PRIMARY KEY,
    student_id      INT         NOT NULL,
    club_id         INT         NOT NULL,
    join_date       DATE        NOT NULL,
    role            VARCHAR(50) DEFAULT 'Member',
    status          VARCHAR(20) DEFAULT 'Active',
    CONSTRAINT fk_membership_student
        FOREIGN KEY (student_id) REFERENCES Student(student_id),
    CONSTRAINT fk_membership_club
        FOREIGN KEY (club_id)    REFERENCES Club(club_id),
    CONSTRAINT uc_membership_unique
        UNIQUE (student_id, club_id)
);

CREATE TABLE Event (
    event_id        INT PRIMARY KEY,
    club_id         INT          NOT NULL,
    title           VARCHAR(150) NOT NULL,
    description     TEXT,
    event_date      DATE         NOT NULL,
    start_time      TIME         NOT NULL,
    end_time        TIME,
    venue           VARCHAR(150),
    capacity        INT,
    CONSTRAINT chk_capacity_positive CHECK (capacity > 0),
    CONSTRAINT fk_event_club
        FOREIGN KEY (club_id) REFERENCES Club(club_id)
);

CREATE TABLE Participation (
    participation_id    INT PRIMARY KEY,
    event_id            INT         NOT NULL,
    student_id          INT         NOT NULL,
    registration_date   DATE        NOT NULL,
    attendance_status   VARCHAR(20) DEFAULT 'Registered',
    CONSTRAINT fk_participation_event
        FOREIGN KEY (event_id) REFERENCES Event(event_id),
    CONSTRAINT fk_participation_student
        FOREIGN KEY (student_id) REFERENCES Student(student_id),
    CONSTRAINT uc_event_student_unique
        UNIQUE (event_id, student_id)
);

Students
INSERT INTO Student (student_id, first_name, last_name, email, course, year_of_study) VALUES
(2001, 'Amira', 'Khan', 'amira.khan@example.ac.uk', 'Computer Science', 2),
(2002, 'Liam', 'Owen', 'liam.owen@example.ac.uk', 'Business Management', 1),
(2003, 'Sofia', 'Lopez', 'sofia.lopez@example.ac.uk', 'Mechanical Engineering', 3),
(2004, 'James', 'Chen', 'james.chen@example.ac.uk', 'Mathematics', 2),
(2005, 'Elena', 'Smith', 'elena.smith@example.ac.uk', 'Law', 1);

INSERT INTO Club (club_id, club_name, category, founding_date, description) VALUES
(1, 'Tech Society', 'Academic', '2015-10-01', 'Workshops, talks and hackathons about technology.'),
(2, 'Film Club', 'Cultural', '2010-03-15', 'Weekly film screenings and discussions.'),
(3, 'Football Club', 'Sports', '2008-09-01', 'Training sessions and university league matches.'),
(4, 'Debate Society', 'Academic', '2012-01-20', 'Debating competitions and public speaking.'),
(5, 'Art Club', 'Cultural', '2016-04-12', 'Creative drawing, painting and exhibitions.');

INSERT INTO Membership (membership_id, student_id, club_id, join_date, role, status) VALUES
(1, 2001, 1, '2025-09-20', 'President', 'Active'),
(2, 2002, 1, '2025-09-22', 'Member', 'Active'),
(3, 2003, 3, '2025-09-21', 'Member', 'Active'),
(4, 2001, 2, '2025-09-25', 'Member', 'Active'),
(5, 2004, 2, '2025-09-28', 'Member', 'Active'),
(6, 2005, 5, '2025-10-01', 'Member', 'Active');

INSERT INTO Event (event_id, club_id, title, description, event_date, start_time, end_time, venue, capacity) VALUES
(10, 1, 'Intro to Databases Workshop', 'Beginner workshop', '2025-10-10', '17:00', '19:00', 'Lab 3', 40),
(11, 2, 'Halloween Film Night', 'Horror movie screening', '2025-10-31', '19:00', '23:00', 'SU Cinema', 80),
(12, 3, 'Friendly 5-a-side Match', 'Casual football match', '2025-10-15', '16:00', '18:00', 'Sports Hall', 20),
(13, 4, 'Debate Competition', 'Inter-university challenge', '2025-11-05', '13:00', '16:00', 'Lecture Theatre B', 50),
(14, 5, 'Art Workshop', 'Beginner art session', '2025-10-20', '14:00', '16:00', 'Art Room 1', 25);

INSERT INTO Participation (participation_id, event_id, student_id, registration_date, attendance_status) VALUES
(100, 10, 2001, '2025-10-01', 'Registered'),
(101, 10, 2002, '2025-10-02', 'Registered'),
(102, 11, 2001, '2025-10-20', 'Registered'),
(103, 12, 2003, '2025-10-05', 'Registered'),
(104, 13, 2004, '2025-10-25', 'Registered'),
(105, 14, 2005, '2025-10-12', 'Registered');

DELIMITER $$

CREATE PROCEDURE register_participation(
    IN p_student_id INT,
    IN p_event_id INT
)
BEGIN
    DECLARE event_capacity INT;
    DECLARE current_count INT;

    SELECT capacity INTO event_capacity
    FROM Event WHERE event_id = p_event_id;

    SELECT COUNT(*) INTO current_count
    FROM Participation WHERE event_id = p_event_id;

    IF current_count >= event_capacity THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Event is full';
    ELSE
        INSERT INTO Participation (event_id, student_id, registration_date)
        VALUES (p_event_id, p_student_id, CURDATE());
    END IF;
END $$

DELIMITER ;


DELIMITER $$

CREATE TRIGGER log_membership_status_change
BEFORE UPDATE ON Membership
FOR EACH ROW
BEGIN
    IF OLD.status <> NEW.status THEN
        INSERT INTO MembershipAudit (membership_id, old_status, new_status)
        VALUES (OLD.membership_id, OLD.status, NEW.status);
    END IF;
END $$

DELIMITER ;

Indexes were added to improve the performance of common queries:
CREATE INDEX idx_membership_student ON Membership(student_id);
CREATE INDEX idx_membership_club ON Membership(club_id);
CREATE INDEX idx_event_club_date ON Event(club_id, event_date);
CREATE INDEX idx_participation_event ON Participation(event_id);
CREATE INDEX idx_participation_student ON Participation(student_id);


